import { trim, merge, get } from 'lodash';
import { Op, Sequelize } from 'sequelize';

import { getSequelize } from '../db';
import { getOptions } from '../utils/sql-helper';
import Outlets from '../models/outlet';
import TransactionDetails from '../models/transaction-detail';
import TransactionCodes from '../models/transaction-code';
import MemberCards from '../models/member-card';
import Users from '../models/user';
import MemberCardLogs from '../models/member-card-log';
import Names from '../models/name';

// order docs https://sequelize.org/docs/v6/core-concepts/model-querying-basics/
class ReportController {
  // 收银报表
  async cashierPayments(req, res, next) {
    try {
      const {
        begin_date, end_date, cashier_id, search_type
      } = req.query; // search_type: Detail, GroupByDate, GroupByCashier
      const { hotel_id } = req.user;
      const sequelize = getSequelize();
      let conditions = '';
      let sql = '';
      let data = [];
      if (cashier_id) {
        conditions = `AND t.created_id = '${cashier_id}'`;
      }

      if (search_type === 'Detail') {
        data = await TransactionDetails.findAndCountAll(getOptions(req, () => {
          const where = {
            hotel_id,
            transaction_type: 'PAYMENT',
            business_date: {
              [Op.between]: [begin_date, end_date]
            }
          };

          if (cashier_id) {
            where.created_id = cashier_id;
          }

          return {
            where,
            order: [['created_at', 'DESC']],
            include: [{
              model: TransactionCodes,
              as: 'transaction_code',
              required: true,
              attributes: ['code', 'name']
            }, {
              model: Users,
              as: 'cashier',
              required: true,
              attributes: ['login_name', 'xfull_name']
            }]
          };
        }));
      }
      else if (search_type === 'GroupByDate') {
        sql = `SELECT
        business_date,
        transaction_code_id,
        any_value ( c.name ) transaction_code_name,
        sum( CASE WHEN amount > 0 THEN 1 ELSE 0 END ) quantity,
        sum( CASE WHEN amount < 0 THEN 1 ELSE 0 END ) quantity_refund,
        sum( CASE WHEN amount > 0 THEN amount ELSE 0 END ) total_amount,
        sum( CASE WHEN amount < 0 THEN amount ELSE 0 END ) refund_amount,
        sum( amount ) net_amount 
      FROM
        transaction_details t,
        transaction_codes c 
      WHERE
        t.hotel_id = '${hotel_id}' 
        AND business_date BETWEEN '${begin_date}' AND '${end_date}' 
        AND t.transaction_code_id = c.id 
        AND t.transaction_type = 'PAYMENT'
        ${conditions}
      GROUP BY
        business_date,
        t.transaction_code_id 
      ORDER BY
        business_date,
        any_value ( c.sequence )`;
        data = { rows: await sequelize.query(sql, { type: 'SELECT' }) };
      }
      else if (search_type === 'GroupByCashier') {
        sql = `SELECT
        t.created_id,
        any_value ( u.login_name ) cashier_name,
        transaction_code_id,
        any_value ( c.name ) transaction_code_name,
        sum( CASE WHEN amount > 0 THEN 1 ELSE 0 END ) quantity,
        sum( CASE WHEN amount < 0 THEN 1 ELSE 0 END ) quantity_refund,
        sum( CASE WHEN amount > 0 THEN amount ELSE 0 END ) total_amount,
        sum( CASE WHEN amount < 0 THEN amount ELSE 0 END ) refund_amount,
        sum( amount ) net_amount 
      FROM
        transaction_details t,
        transaction_codes c,
        users u
      WHERE
        t.hotel_id = '${hotel_id}' 
        AND business_date BETWEEN '${begin_date}' AND '${end_date}' 
        AND t.transaction_code_id = c.id 
        AND t.transaction_type = 'PAYMENT' 
        and t.created_id = u.id
        ${conditions}
      GROUP BY
        t.created_id,
        t.transaction_code_id 
      ORDER BY
        any_value ( u.login_name ),
        any_value ( c.sequence )`;
        data = { rows: await sequelize.query(sql, { type: 'SELECT' }) };
      }

      res.sendOk(data);
    }
    catch (e) {
      next(e);
    }
  }

  // 销售业绩
  async salerPerformance(req, res, next) {
    try {
      const {
        begin_date, end_date, name, order
      } = req.query;
      const { hotel_id } = req.user;
      const sequelize = getSequelize();
      let conditions = '';
      if (name) {
        conditions = `AND (s.name like '%${name}%' or s.code like '%${name}%')`;
      }

      let orderBy = 'code';
      switch (order) {
        case 'code':
          orderBy = 'code asc';
          break;
        case '-code':
          orderBy = 'code desc';
          break;
        case 'quantity':
          orderBy = 'quantity asc';
          break;
        case '-quantity':
          orderBy = 'quantity desc';
          break;
        case 'quantity_refund':
          orderBy = 'quantity_refund asc';
          break;
        case '-quantity_refund':
          orderBy = 'quantity_refund desc';
          break;
        case 'total_amount':
          orderBy = 'total_amount asc';
          break;
        case '-total_amount':
          orderBy = 'total_amount desc';
          break;
        case 'refund_amount':
          orderBy = 'refund_amount asc';
          break;
        case '-refund_amount':
          orderBy = 'refund_amount desc';
          break;
        case 'net_amount':
          orderBy = 'net_amount asc';
          break;
        case '-net_amount':
          orderBy = 'net_amount desc';
          break;
        default:
      }

      let sql = `SELECT * FROM (SELECT
      s.code,
      any_value ( s.name ) name,
      sum( CASE WHEN amount > 0 THEN 1 ELSE 0 END ) quantity,
      sum( CASE WHEN amount < 0 THEN 1 ELSE 0 END ) quantity_refund,
      sum( CASE WHEN amount > 0 THEN amount ELSE 0 END ) total_amount,
      sum( CASE WHEN amount < 0 THEN amount ELSE 0 END ) refund_amount,
      sum( amount ) net_amount 
    FROM
      transaction_details t
      LEFT JOIN salers s ON t.saler_id = s.id 
    WHERE
      t.hotel_id = '${hotel_id}'
      AND business_date BETWEEN '${begin_date}' AND '${end_date}' 
      AND transaction_type = 'PAYMENT' 
      ${conditions}
    GROUP BY
      s.code) A
    ORDER BY
      ${orderBy}`;

      res.sendOk(await sequelize.query(sql, { type: 'SELECT' }));
    }
    catch (e) {
      next(e);
    }
  }

  // 每日交易明细
  async cashierJournal(req, res, next) {
    try {
      const {
        begin_date, end_date, cashier_id, member_number,
        outlet_id, transaction_type, transaction_code_id,
        card_type, member_type_id, member_level_id
      } = req.query;

      const { hotel_id } = req.user;

      let memberCardConditions = {};

      if (card_type) {
        memberCardConditions.card_type = card_type;
      }

      if (member_type_id) {
        memberCardConditions.member_type_id = member_type_id;
      }

      if (member_level_id) {
        memberCardConditions.member_level_id = member_level_id;
      }

      const options = getOptions(req, () => {
        const where = {
          hotel_id,
          business_date: { [Op.between]: [begin_date, end_date] }
        };

        if (cashier_id) {
          where.created_id = cashier_id;
        }

        if (member_number) {
          where.member_number = member_number;
        }

        if (outlet_id) {
          where.outlet_id = outlet_id;
        }

        if (transaction_type) {
          where.transaction_type = transaction_type;
        }

        if (transaction_code_id) {
          where.transaction_code_id = transaction_code_id;
        }

        return { where };
      }, () => {
        const include = [{
          model: TransactionCodes,
          as: 'transaction_code',
          attributes: ['code', 'name']
        }, {
          model: Users,
          as: 'cashier',
          attributes: ['login_name']
        }, {
          model: Outlets,
          as: 'outlet',
          attributes: ['name']
        }];

        include.push({
          model: MemberCards,
          as: 'member_card',
          attributes: ['member_number', 'name_id'],
          required: true,
          where: memberCardConditions,
          include: [{
            model: Names,
            as: 'name',
            attributes: ['last_name', 'first_name', 'xfull_name']
          }]
        });

        return { include };
      });

      const data = await TransactionDetails.findAndCountAll(options);
      const opts = {
        where: options.where,
        attributes: [[Sequelize.fn('sum', Sequelize.col('amount')), 'amount']],
        raw: true
      };

      if (Object.keys(memberCardConditions).length > 0) {
        opts.include = [{
          model: MemberCards,
          as: 'member_card',
          attributes: [],
          required: true,
          where: memberCardConditions
        }];
      }

      const total = await TransactionDetails.findAll(opts);

      data.totalAmount = get(total, '0.amount', 0);
      res.sendOk(data);
    }
    catch (e) {
      next(e);
    }
  }

  // 交易调整
  async transactionRebate(req, res, next) {
    try {
      const {
        begin_date, end_date, cashier_id, member_number, transaction_type
      } = req.query;
      const { hotel_id } = req.user;

      const options = getOptions(req, () => {
        const where = {
          hotel_id,
          amount: { [Op.lt]: 0 },
          business_date: { [Op.between]: [begin_date, end_date] }
        };

        if (cashier_id) {
          where.created_id = cashier_id;
        }

        if (member_number) {
          where.member_number = member_number;
        }

        if (transaction_type) {
          where.transaction_type = transaction_type;
        }

        return { where };
      }, () => ({
        include: [{
          model: TransactionCodes,
          as: 'transaction_code',
          attributes: ['code', 'name']
        }, {
          model: Users,
          as: 'cashier',
          attributes: ['login_name']
        }]
      }));

      const data = await TransactionDetails.findAndCountAll(merge(options, { raw: true }));
      if (data && Array.isArray(data.rows)) {
        for (const row of data.rows) {
          row.hasChildren = true;
          row.children = await TransactionDetails
            .findAll({
              where: { id: row.refund_from_transaction_detail_id },
              include: [{
                model: TransactionCodes,
                as: 'transaction_code',
                attributes: ['code', 'name']
              }, {
                model: Users,
                as: 'cashier',
                attributes: ['login_name']
              }],
              raw: true,
              order: [['created_at', 'DESC']]
            });
        }
      }
      res.sendOk(data);
    }
    catch (e) {
      next(e);
    }
  }

  // 会员卡变更日志
  async memberCardChangeLogs(req, res, next) {
    try {
      const {
        begin_time, end_time, created_id, member_number, action_type, reference
      } = req.query;
      const { hotel_id } = req.user;

      const options = getOptions(req, () => {
        const where = {
          hotel_id,
          created_at: { [Op.between]: [begin_time, end_time] }
        };

        if (created_id) {
          where.created_id = created_id;
        }

        if (member_number) {
          where.member_number = member_number;
        }

        if (action_type) {
          where.action_type = action_type;
        }

        if (reference) {
          where.reference = { [Op.like]: `%${trim(reference)}%` };
        }

        return { where };
      }, () => ({
        include: [{
          model: Names,
          as: 'name'
        }, {
          model: Users,
          as: 'user'
        }]
      }));

      const data = await MemberCardLogs.findAndCountAll(options);

      res.sendOk(data);
    }
    catch (e) {
      next(e);
    }
  }
}

export default new ReportController();
